﻿using System;
using System.Data.SQLite;
using System.Diagnostics;

namespace Workbench.Persistance
{
    public class DBContext : IDisposable
    {
        private string connectionString;
        private SQLiteConnection activeConnection;
        public string projectDbFilePath;

        public static readonly DBContext Current = new DBContext();
        public void Initialize(string projectDbFilePath)
        {
            connectionString = DbUtility.GetConnectionString(projectDbFilePath);
            activeConnection = DbUtility.GetConnection(connectionString);
            IsInitialized = true;
            this.projectDbFilePath = projectDbFilePath;
        }

        public void UnInitialize()
        {
            connectionString = null;
            if (activeConnection != null)
            {
                try
                {
                    activeConnection.Close();
                }
                catch (Exception ex)
                {
                    Trace.WriteLine(ex.ToString());
                }

                activeConnection.Dispose();
                activeConnection = null;
            }
            IsInitialized = false;
        }
        public bool IsInitialized { get; private set; }

        public BuildRepository GetBuildRepository()
        {
            return new BuildRepository(() =>
            {
                return DbUtility.GetConnection(connectionString);
            });
        }

        public ExerciseRepository GetExercisesRepository()
        {
            return new ExerciseRepository(() =>
            {
                return DbUtility.GetConnection(connectionString);
            });
        }
        public TextRepository GetTextRepository()
        {
            if (activeConnection == null) return null;
            return new TextRepository(() => activeConnection);
        }

        public LemmatizerRepository GetLemmatizerRepository()
        {
            if (activeConnection == null) return null;
            return new LemmatizerRepository(() => activeConnection);
        }

        public ConcordancerRepository GetConcordancerRepository()
        {
            if (activeConnection == null) return null;
            return new ConcordancerRepository(() => activeConnection);
        }

        public POSTaggerRepository GetPOSTaggerRepository()
        {
            if (activeConnection == null) return null;
            return new POSTaggerRepository(() => activeConnection);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (activeConnection != null)
                {
                    activeConnection.Dispose();
                    activeConnection = null;
                }
            }
        }

        public void CreateTablesAndIndexes1()
        {
            DbUtility.ExecuteNonQuery(activeConnection,
@"CREATE TABLE Files (
    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    ParentDirectoryId NUMERIC NOT NULL,
    FileContentId NUMERIC NOT NULL,
    ContextIncluded NUMERIC NOT NULL,
    FOREIGN KEY(ParentDirectoryId) REFERENCES Directories(RowId) ON DELETE CASCADE,
    FOREIGN KEY(FileContentId) REFERENCES FileContents(RowId) ON DELETE CASCADE
);

CREATE TABLE FileContents (
    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
    Content TEXT NOT NULL
);   

CREATE TABLE FileStatistics (
    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
    FileId INTEGER NOT NULL,
    Paragraphs INTEGER,
    Sentences INTEGER,
    TextLengthInRunningWords INTEGER,
    DifferentForms INTEGER,
    MaximumNumberOfSentencesInParagraph INTEGER,
    MaximumNumberOfWordsInSentence INTEGER,
    AverageLengthOfSentenceInWords REAL,
    AverageLengthOfParagraphInWords REAL,
    AverageLengthOfParagraphInSentences REAL,
    FOREIGN KEY(FileId) REFERENCES Files(RowId) ON DELETE CASCADE
);");
            DbUtility.ExecuteNonQuery(activeConnection,
@"CREATE INDEX IX_Files_ParentDirectoryId ON Files (ParentDirectoryId);");

            DbUtility.ExecuteNonQuery(activeConnection,
@"CREATE INDEX IX_FileStatistics_FileId ON FileStatistics (FileId);");

            DbUtility.ExecuteNonQuery(activeConnection, @"
CREATE TABLE Directories (
    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    ParentDirectoryId NUMERIC NULL,
    FOREIGN KEY(ParentDirectoryId) REFERENCES Directories(rowid) ON DELETE CASCADE
);
            ");

            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE INDEX IX_Directories_ParentDirectoryId ON Directories (ParentDirectoryId);");
        }

        public void CreateTablesAndIndexes2()
        {
            DbUtility.ExecuteNonQuery(activeConnection, @"
                CREATE TABLE Paragraphs (
                    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
                    Length NUMERIC NOT NULL,
                    StartPosition NUMERIC NOT NULL,
                    FileId NUMERIC NOT NULL,
                    FOREIGN KEY(FileId) REFERENCES Files(RowId) ON DELETE CASCADE
                );
            ");
            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE INDEX IX_Paragraphs_FileId ON Paragraphs (FileId);");


            DbUtility.ExecuteNonQuery(activeConnection, @"
                CREATE TABLE Sentences (
                    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
                    Length NUMERIC NOT NULL,
                    StartPosition NUMERIC NOT NULL,
                    ParagraphId NUMERIC NOT NULL,
                    FOREIGN KEY(ParagraphId) REFERENCES Paragraphs(RowId) ON DELETE CASCADE
                );
            ");
            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE INDEX IX_Sentences_ParagraphId ON Sentences (ParagraphId);");


            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE TABLE UniqueWords ( 
                    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
                    Content TEXT NOT NULL UNIQUE
                );
            ");

            DbUtility.ExecuteNonQuery(activeConnection, @"
                CREATE TABLE BaseForms (
                    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
                    Content TEXT NOT NULL UNIQUE
                );
            ");

            DbUtility.ExecuteNonQuery(activeConnection, @"
                CREATE TABLE Words (
                    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
                    SentenceId NUMERIC,
                    UniqueWordId NUMERIC,
                    BaseFormId NUMERIC,
                    FOREIGN KEY(SentenceId) REFERENCES Sentences(RowId) ON DELETE CASCADE,
                    FOREIGN KEY(UniqueWordId) REFERENCES UniqueWords(RowId),
                    FOREIGN KEY(BaseFormId) REFERENCES BaseForms(RowId)
                );
            ");
            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE INDEX IX_Words_SentenceId ON Words (SentenceId);");

            DbUtility.ExecuteNonQuery(activeConnection, @"
                CREATE TABLE POSTypes (
                    RowId INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    ParentPOSTypeId NUMERIC NULL,
                    FOREIGN KEY(ParentPOSTypeId) REFERENCES POSTypes(RowId) ON DELETE CASCADE
                );
            ");

            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE INDEX IX_POSTypes_ParentPOSTypeId ON POSTypes (ParentPOSTypeId);");

            DbUtility.ExecuteNonQuery(activeConnection, @"
                CREATE TABLE Words_POSTypes (
                    WordId NUMERIC,
                    POSTypeId NUMERIC,
                    PRIMARY KEY ( WordId, POSTypeId ),
                    FOREIGN KEY(WordId) REFERENCES Words(RowId) ON DELETE CASCADE,
                    FOREIGN KEY(POSTypeId) REFERENCES POSTypes(RowId) ON DELETE CASCADE
                );
            ");
            DbUtility.ExecuteNonQuery(activeConnection,@"CREATE INDEX IX_Words_POSTypes_WordId ON Words_POSTypes (WordId);");
            DbUtility.ExecuteNonQuery(activeConnection, @"CREATE INDEX IX_Words_POSTypes_POSTypeId ON Words_POSTypes (POSTypeId);");

            //TODO: Indexes
            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE TABLE ExerciseSuites (
                    RowId INTEGER PRIMARY KEY AUTOINCREMENT, 
                    Name TEXT
                );");
            DbUtility.ExecuteNonQuery(activeConnection, 
                @"CREATE TABLE Exercises (
                    RowId INTEGER PRIMARY KEY, 
                    Name TEXT, 
                    UniqueWordId NUMERIC, 
                    BaseFormId NUMERIC, 
                    POSTypeId NUMERIC
                );");
            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE TABLE ExerciseSuites_Exercises (
                    ExerciseSuiteId NUMERIC, 
                    ExerciseId NUMERIC, 
                    DisplayOrder NUMERIC,

                    FOREIGN KEY(ExerciseSuiteId) REFERENCES ExerciseSuites(RowId) ON DELETE CASCADE,
                    FOREIGN KEY(ExerciseId) REFERENCES Exercises(RowId) ON DELETE CASCADE
                );");
            DbUtility.ExecuteNonQuery(activeConnection,
                @"CREATE TABLE Exercises_Sentences (
                    ExerciseId NUMERIC, 
                    SentenceId NUMERIC, 
                    DisplayOrder NUMERIC,

                    FOREIGN KEY(ExerciseId) REFERENCES Exercises(RowId) ON DELETE CASCADE,
                    FOREIGN KEY(SentenceId) REFERENCES Sentences(RowId) ON DELETE CASCADE
                );");
        }
    }
}
